﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Web;
using System.Web.Hosting;
using System.IO;
using Oracle.ManagedDataAccess.Client;
using System.Text;
using Aspose.Cells;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading.Tasks;

namespace XBJCYGC.Controllers
{
    public class SYSController : ApiController
    {

        private void doSave(JObject opt, bool bDirty)
        {
            string table = opt["_TABLE"].ToString();
            string field = opt["_FIELD"].ToString();
            string value = opt[field].ToString().Trim();
            Dictionary<string, string> dict = new Dictionary<string, string>();

            //主键列,拼出对应的主键条件来
            List<string> keyList = new List<string>();
            foreach (var kv in opt)
            {
                if (kv.Key != "_TABLE" && kv.Key != "_FIELD" && kv.Key != field && !kv.Key.StartsWith("old_"))
                {
                    keyList.Add(kv.Key + "='" + kv.Value + "'");
                }
            }
            if (keyList.Count == 0)
            {
                throw new Exception("没有找到更新的主键，不能进行更新！");
            }
            string keyStr = string.Join(" and ", keyList);

            //始终当做字符串插入。数字列和字符列都没问题
            string sql = bDirty ? "update {0} set isdirty='1' ,{1}='{2}' where {3}" : "update {0} set {1}='{2}' where {3}";

            sql = string.Format(sql, table, field, value, keyStr);
            try
            {
                DB.CYGC.Execute(sql);
            }
            catch (OracleException ex)
            {
                //ora-01840:输入 的日期不够长。表示列是日期列。
                if (ex.ErrorCode == 1840 || ex.ErrorCode == 1861)
                {
                    sql = bDirty ? "update {0} set isdirty='1' ,{1}=to_date('{2}','yyyy-mm-dd') where {3}" : "update {0} set {1}=to_date('{2}','yyyy-mm-dd') where {3}";
                    sql = string.Format(sql, table, field, value, keyStr);
                    DB.CYGC.Execute(sql);
                }
                else //如果遇到其它类型的数据列，在些增加更多的判断
                {
                    throw ex;
                }
            }
        }
        [CYGCExceptionFilter("保存新值错误")]
        public void SAVE(JObject opt)
        {
            doSave(opt, false);
        }
        [CYGCExceptionFilter("保存数值时错误")]
        public void SAVEwithDirty(JObject opt)
        {
            doSave(opt, true);
        }

        [CYGCExceptionFilter("保存时错误")]
        public void FormSave(JObject opt)
        {
            var table = opt["_table"].ToString().Trim();
            //目前仅支持单列的主键。联合主键先不考虑
            var keys = opt["_keys"].ToString().Trim();

            string[] filterArr = { "_table", "_keys" };
            string sql;
            List<string> fields = new List<string>();
            List<string> values = new List<string>();


            //1、 如果不存在主键，表示是新增.则主键补一个GUID
            if (opt[keys].ToString().Trim() == "")
            {
                foreach (var kv in opt)
                {
                    if (filterArr.Contains(kv.Key)) continue;
                    //如果是主键，则要生成一个id
                    if (kv.Key == keys)
                    {
                        fields.Add(kv.Key);
                        values.Add($"'{Guid.NewGuid().ToString("N")}'");
                    }
                    else if (kv.Key.EndsWith("_RQ"))
                    {
                        fields.Add(kv.Key.Substring(0, kv.Key.Length - 3));
                        values.Add($"to_date('{kv.Value}','yyyy-mm-dd')");
                    }
                    else
                    {
                        fields.Add(kv.Key);
                        values.Add($"'{kv.Value.ToString()}'");
                    }
                }
                sql = $"insert into {table} ({string.Join(",", fields)}) values ({ string.Join(",", values)})";
            }
            else
            {
                foreach (var kv in opt)
                {
                    if (filterArr.Contains(kv.Key) || kv.Key == keys) continue;
                    if (kv.Key.EndsWith("_RQ"))
                    {
                        fields.Add($"{kv.Key.Substring(0, kv.Key.Length - 3)}=to_date('{kv.Value}','yyyy-mm-dd')");
                    }
                    else
                    {
                        fields.Add($"{kv.Key}='{kv.Value}'");
                    }
                }
                sql = $"update {table} set { string.Join(",", fields)} where {keys}='{opt[keys].ToString()}'";
            }
            DB.CYGC.Execute(sql);
        }
        

        [CYGCExceptionFilter("上传文件时错误")]
        public async Task<object> FileUpload()
        {
            if (!Request.Content.IsMimeMultipartContent())
            {
                throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
            }
            Dictionary<string, string> dic = new Dictionary<string, string>();
            string root = HttpContext.Current.Server.MapPath("~");//指定要将文件存入的服务器物理位置  
            var provider = new MultipartMemoryStreamProvider();

            await Request.Content.ReadAsMultipartAsync(provider);
            if (provider.Contents.Count == 0)
            {
                throw new Exception("没有发现上传文件！");
            }
            var httpcontent = provider.Contents[0];
            var stream = await httpcontent.ReadAsStreamAsync();

            FileStream fs = new FileStream("D:\\1.zip", FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(fs);
            stream.CopyTo(sw.BaseStream);
            sw.Flush();
            sw.Close();
            return "";
        }
        [CYGCExceptionFilter("上传文件时错误")]
        public object FileUpload2()
        {
            HttpPostedFile file = HttpContext.Current.Request.Files[0];
            string strPath = "e:\\tmp\\"+file.FileName;
            file.SaveAs(strPath);
            return new { ret = "ok" };
        }




        [HttpPost]
        [CYGCExceptionFilter("导出时错误")]
        public HttpResponseMessage Export(JObject opt)
        {
            //只用得到data,上下表头header，headerFields ,isRownumber，filter等，不进行merge
            int cnt = int.Parse(opt["cnt"].ToString());
            StringBuilder sb = new StringBuilder();
            for (int i = 1; i <= cnt; i++)
            {
                sb.Append(opt["d" + i].ToString());
            }
            StringReader sr = new StringReader(sb.ToString());
            ExportFmt fmt = new Newtonsoft.Json.JsonSerializer().Deserialize(sr, typeof(ExportFmt)) as ExportFmt;

            Workbook wb = new Workbook();
            Worksheet ws = wb.Worksheets[0];//默认就有一张表
            var cells = ws.Cells;
            var bordertype = CellBorderType.Thin;
            Style headStyle = new Style();
            headStyle.Font.Name = "宋体";
            headStyle.Font.Size = 12;
            headStyle.Borders[BorderType.BottomBorder].LineStyle = bordertype;
            headStyle.Borders[BorderType.TopBorder].LineStyle = bordertype;
            headStyle.Borders[BorderType.LeftBorder].LineStyle = bordertype;
            headStyle.Borders[BorderType.RightBorder].LineStyle = bordertype;
            headStyle.Font.IsBold = true;
            //headStyle.ShrinkToFit = true;
            headStyle.IsTextWrapped = true;
            headStyle.HorizontalAlignment = TextAlignmentType.Center;
            headStyle.VerticalAlignment = TextAlignmentType.Center;

            Style stringStyle = new Style();
            stringStyle.Copy(headStyle);
            stringStyle.HorizontalAlignment = TextAlignmentType.Center;
            stringStyle.Font.IsBold = false;

            Style numberStyle = new Style();
            numberStyle.Copy(stringStyle);
            numberStyle.Number = 0; //参考格式 ：http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers+and+Dates

            int currRow = 0;
            currRow = renderHead(fmt.header, cells, headStyle, currRow);

            ws.FreezePanes(currRow, fmt.freezeFields.Length, currRow, fmt.freezeFields.Length);//锁定表头、列。


            //输出数据
            currRow = renderbody(fmt.showData, fmt.headerFields, fmt.freezeFields, cells, stringStyle, numberStyle, currRow);
            if (fmt.header2.Length > 0)
            {
                currRow = renderHead(fmt.header2, cells, headStyle, currRow);
                currRow = renderbody(fmt.showData, fmt.header2Fields, fmt.freezeFields, cells, stringStyle, numberStyle, currRow);
            }

            setColumnWithAuto(ws);


            //3、输出到HttpResponseMessage
            var stream = new MemoryStream();
            wb.Save(stream, SaveFormat.Excel97To2003);

            HttpResponseMessage result = new HttpResponseMessage();
            ContentDispositionHeaderValue disposition = new ContentDispositionHeaderValue("attachment");
            var filename = "Export_" + DateTime.Today.ToShortDateString() + ".xls";
            disposition.FileName = filename;
            disposition.Name = filename.Replace(".xls", "");

            result.Content = new ByteArrayContent(stream.ToArray());
            result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
            result.Content.Headers.ContentDisposition = disposition;
            return result;
        }

        private static int renderbody(JObject[] data, string[] headerfields, string[] freezeFields, Cells cells, Style stringStyle, Style numberStyle, int currRow)
        {
            int x = 0;
            double tmpDbl;
            Style usedStyle = new Style(); //待使用的样式
            foreach (JObject row in data)
            {
                foreach (string key in headerfields)
                {
                    if (double.TryParse(row.GetValue(key).ToString(), out tmpDbl))
                    {
                        usedStyle.Copy(numberStyle);
                        cells[currRow, x].Value = tmpDbl;
                    }
                    else
                    {
                        usedStyle.Copy(stringStyle);
                        cells[currRow, x].Value = row.GetValue(key);
                    }
                    if (freezeFields.Contains(key))
                    {
                        usedStyle.BackgroundColor = Color.LightGray;  //不知道为什么，颜色增加不上。
                    }
                    cells[currRow, x].SetStyle(usedStyle);
                    x++;
                }
                currRow++;
                x = 0;
            }
            return currRow;
        }
        void setColumnWithAuto(Worksheet sheet)
        {
            Cells cells = sheet.Cells;
            int columnCount = cells.MaxColumn;
            //获取表页的最大列数              
            int rowCount = cells.MaxRow;
            //获取表页的最大行数                            
            for (int col = 0; col < columnCount; col++)
            {
                sheet.AutoFitColumn(col, 0, rowCount);
            }
            for (int col = 0; col < columnCount; col++)
            {
                cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
            }
        }
        private static int renderHead(JArray[] header, Cells cells, Style headStyle, int currRow)
        {
            //保存标志。本来可以用fmt.header.Length来判断行数。但万一用户写的不对，会判断错。所以还最大行是10吧
            bool[,] pos = new bool[10, 200];
            int x = 0, y = 0;

            foreach (JArray row in header)
            {
                foreach (JObject head in row)
                {
                    //KeyValuePair<string, JToken>
                    var i = head["span"].Count();
                    string text = head["title"].ToString();
                    //去除id，标签。把<br />转成换行。把<sup>转成上标 暂时不知道控件如何换行及上下标
                    int splitePos = text.IndexOf("|");
                    if (splitePos > 0) text = text.Substring(0, splitePos);
                    //匹配br
                    text = Regex.Replace(text, @"(<br[^>]*>)", "\n", RegexOptions.IgnoreCase);
                    //匹配上标
                    /**假如匹配情况如下
                     * string     上次变更/n的<sup>日</sup >期   
                     * length                 5        7
                     * index               6   11 12     19
                     * 记录上标的信息：只需要记录开始位置是6，长度是12-5-6的位置即可，然后将这两个<sup></sup >从字符串中移除掉即可
                     * matchList保存的是【6，1】。
                     * */
                    List<int> matchList = new List<int>();
                    var match1 = Regex.Match(text, @"<sup *>", RegexOptions.IgnoreCase);
                    while (match1.Success)
                    {
                        var match2 = Regex.Match(text, @"</sup *>", RegexOptions.IgnoreCase);

                        matchList.Add(match1.Index);
                        matchList.Add(match2.Index - (match1.Index + match1.Length));
                        text = text.Substring(0, match1.Index) + text.Substring(match1.Index + match1.Length, match2.Index - (match1.Index + match1.Length))
                            + text.Substring(match2.Index + match2.Length);
                        match1 = Regex.Match(text, @"<sup *>", RegexOptions.IgnoreCase);
                    }
                    //匹配其它任何标签
                    text = Regex.Replace(text, @"(<[^>]*>)", "", RegexOptions.IgnoreCase);

                    int rowspan = 1, colspan = 1;
                    if (i >= 1) colspan = head["span"][0].Value<int>();
                    if (i >= 2) rowspan = head["span"][1].Value<int>();

                    for (var tmp1 = 0; tmp1 < rowspan; tmp1++)
                    {
                        for (var tmp2 = 0; tmp2 < colspan; tmp2++)
                        {
                            while (pos[y + tmp1, x + tmp2])
                            {
                                x++;
                            }
                            pos[y + tmp1, x + tmp2] = true;
                        }
                    }

                    //执行合并
                    if (rowspan + colspan > 2)
                    {
                        //cells.Merge(y + currRow, x, rowspan, colspan);
                        Range range = cells.CreateRange(y + currRow, x, rowspan, colspan);
                        range.Merge();
                        //range.SetStyle(headStyle);  //
                        cells[y + currRow, x].SetStyle(headStyle);
                        range.Value = text;

                        range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
                        range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
                        range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thin, Color.Black);
                        range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
                    }
                    else
                    {
                        cells[y + currRow, x].SetStyle(headStyle);
                        cells[y + currRow, x].Value = text;
                    }
                    if (matchList.Count > 0)
                    {
                        for (int mpos = 0; mpos < matchList.Count; mpos += 2)
                        {
                            cells[y + currRow, x].Characters(matchList[mpos], matchList[mpos + 1]).Font.IsSuperscript = true;
                        }
                    }

                    x += colspan;
                }
                x = 0;
                y++;
            }
            return y + currRow;
        }
        public class ExportFmt
        {
            public JObject[] showData { get; set; }
            public JArray[] header { get; set; }
            public JArray[] header2 { get; set; }
            public string[] headerFields { get; set; }
            public string[] header2Fields { get; set; }
            public string[] freezeFields { get; set; }
        }


    }
}
